package com.tapstitch.scm.common.utils.excel;


import com.tapstitch.scm.common.utils.ImageSizeUtil;
import com.tapstitch.scm.common.utils.excel.annotation.ExcelColumn;
import com.tapstitch.scm.common.utils.excel.annotation.ExcelColumnGroup;
import com.tapstitch.scm.common.utils.excel.exception.ExcelException;
import com.google.common.collect.Lists;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.awt.image.BufferedImage;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.math.BigDecimal;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.file.Files;
import java.nio.file.Path;
import java.util.*;

import static com.tapstitch.scm.common.utils.excel.annotation.ExcelColumn.CellValueType.*;

public class ExcelUtils {


    public static HSSFWorkbook createWorkbook(){
        return new HSSFWorkbook();
    }

    public static XSSFWorkbook createXSSFWorkbook(){
        return new XSSFWorkbook();
    }

    public static void closeWorkbook(Workbook workbook){
        if (workbook != null){
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static void writeToExcel(OutputStream outputStream, String sheetName, String[] columnName, String[][] dataList,boolean containsPic, String title, String footer) {
        if (outputStream == null || !StringUtils.hasText(sheetName) || columnName == null || columnName.length == 0 || dataList == null) {
            return;
        }
        HSSFWorkbook workbook = createWorkbook();
        try {
            writeData(workbook,sheetName,columnName,dataList,containsPic,title,footer);
            workbook.write(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            closeWorkbook(workbook);
        }
    }

    /**
     * 低版本Excel,只能导出65536行以下数据  .xls类型
     * @param excelSheetModel
     * @return
     */
    public static OutputStream writeToExcel(ExcelSheetModel excelSheetModel){
        HSSFWorkbook workbook = createWorkbook();
        OutputStream outputStream = new ByteArrayOutputStream();
        writeToExcel(workbook, excelSheetModel);
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            closeWorkbook(workbook);
        }
        return outputStream;
    }

    /**
     * 高版本，行数不限         .xlsx类型
     * @param excelSheetModel
     * @return
     */
    public static OutputStream writeToXExcel(ExcelSheetModel excelSheetModel){
        XSSFWorkbook workbook = createXSSFWorkbook();
        OutputStream outputStream = new ByteArrayOutputStream();
        writeToExcel(workbook, excelSheetModel);
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            closeWorkbook(workbook);
        }
        return outputStream;
    }

    /**
     * 高版本，行数不限         .xlsx类型
     * @param excelSheetModel
     * @return
     */
    public static void writeToXExcelFile(Path path, ExcelSheetModel excelSheetModel){
        XSSFWorkbook workbook = createXSSFWorkbook();
        writeToExcel(workbook, excelSheetModel);
        try (OutputStream outputStream = Files.newOutputStream(path)) {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            closeWorkbook(workbook);
        }
    }

    public static OutputStream writeToExcelMultiSheet(List<ExcelSheetModel> sheetModelList){

        HSSFWorkbook workbook = createWorkbook();
        OutputStream outputStream = new ByteArrayOutputStream();
        for (ExcelSheetModel sheetModel : sheetModelList){
            writeToExcel(workbook,sheetModel);
        }
        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            closeWorkbook(workbook);
        }

        return outputStream;
    }

    private static void writeToExcel(Workbook workbook, ExcelSheetModel excelSheetModel){

        String sheetName = excelSheetModel.getSheetName();
        List<ColumnMeta> columnMeta = getColumnMeta(excelSheetModel.getRowDataClass(), excelSheetModel.getSpecificColumnFields());
        List rows = excelSheetModel.getRows();
        String title = excelSheetModel.getTitle();
        String footer = excelSheetModel.getFooter();


        if (!StringUtils.hasText(sheetName) || columnMeta == null || columnMeta.size() == 0 || rows == null) {
            throw new ExcelException("ExcelSheetModel 参数错误");
        }

        try {
            if (workbook == null) {
                workbook = new XSSFWorkbook();
            }
            //创建sheet
            Sheet sheet = workbook.createSheet(sheetName);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);
            printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
            //设置列宽度
            for (int i = 0; i < columnMeta.size(); i++) {
                sheet.setColumnWidth(i, 256 * 20);
            }

            //行号
            int rownum = 0;
            if (StringUtils.hasText(title)) {
                //标题行
                Row titleRow = sheet.createRow(rownum++);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellValue(title);
                //标题行单元格样式
                Font titleFont = workbook.createFont();
                titleFont.setFontHeightInPoints((short) 12);
                titleFont.setBold(false);
                titleFont.setFontName("宋体");
                CellStyle titleStyle = workbook.createCellStyle();
                titleStyle.setAlignment(HorizontalAlignment.CENTER);
                titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                titleStyle.setFont(titleFont);
                titleCell.setCellStyle(titleStyle);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnMeta.size() - 1));
            }


            //根据需求合并单元格
            List<Integer> mergeCellList = processMergeCell(excelSheetModel.rows);
            if(!CollectionUtils.isEmpty(mergeCellList)) {
                mergeCell(sheet, 1, mergeCellList, processMergeCellCols(columnMeta));
            }


            //设置列头
            Row columnHeaderRow = sheet.createRow(rownum++);
            CellStyle columnHeaderStyle = workbook.createCellStyle();
            // 设置单元格为水平对齐的类型
            columnHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            columnHeaderStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font font = workbook.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setBold(false);
            font.setFontName("宋体");
            columnHeaderStyle.setFont(font);
            int cellLength = columnMeta.size();
            for (int i = 0; i < cellLength; i++) {
                Cell cell = columnHeaderRow.createCell(i);
                cell.setCellValue(columnMeta.get(i).getColumnName());
                cell.setCellStyle(columnHeaderStyle);
            }

            CellStyle cellStyle = workbook.createCellStyle();

            // 设置单元格为水平对齐的类型
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            Font font2 = workbook.createFont();
            font2.setFontHeightInPoints((short) 12);
            font2.setBold(false);
            font2.setFontName("宋体");
            cellStyle.setFont(font2);
            cellStyle.setWrapText(true);

            Map<String, CellStyle> columnStyle = getColumnStyle(columnMeta, cellStyle, workbook);



            // 添加数据
            int index = rownum++;
            for (int i = 0; i < rows.size(); i++) {
                Row dataRow = sheet.createRow(index);
                short rowHeight = (short) (2000);
                dataRow.setHeight(rowHeight);

                List<Object> childrenRows = null;
                for (int j = 0; j < columnMeta.size(); j++) {
                    if(columnMeta.get(j).getParentClassField() != null) {
                        childrenRows = getChildrenRows(rows.get(i), columnMeta.get(j).getParentClassField());
                        break;
                    }
                }
                for (int j = 0; j < columnMeta.size(); j++) {

                    //判断是哪个注解
                    boolean isChild = columnMeta.get(j).getParentClassField() != null;
                    if (!isChild) {
                        Cell cell2 = dataRow.createCell(j);
                        setCellValue(rows.get(i), columnMeta.get(j), cell2, sheet, workbook);
                        cell2.setCellStyle(columnStyle.getOrDefault(columnMeta.get(j).getClassField(), cellStyle));
                    } else {

                        for (int i1 = 0; i1 < childrenRows.size(); i1++) {
                            Cell cell2;
                            if(i1 == 0) {
                                cell2 = dataRow.createCell(j);
                            }else {
                                int internalIndex = index + i1;
                                Row dataRowChild = sheet.getRow(internalIndex);
                                if(dataRowChild ==  null) {
                                    dataRowChild = sheet.createRow(internalIndex);
                                }
                                dataRowChild.setHeight(rowHeight);
                                cell2 = dataRowChild.createCell(j);
                            }
                            setCellValue(childrenRows.get(i1), columnMeta.get(j), cell2, sheet, workbook);
                            cell2.setCellStyle(columnStyle.getOrDefault(columnMeta.get(j).getClassField(), cellStyle));
                        }
                    }
                }

                if (!CollectionUtils.isEmpty(mergeCellList)) {
                    index += mergeCellList.get(i);
                } else {
                    index++;
                }

            }



            //for (Object row : rows) {
            //    Row dataRow = sheet.createRow(rownum++);
            //    short rowHeight = (short) (700);
            //    dataRow.setHeight(rowHeight);
            //    for (int j = 0; j < columnMeta.size(); j++) {
            //        Cell cell2 = dataRow.createCell(j);
            //
            //        setCellValue(row, columnMeta.get(j), cell2);
            //
            //        cell2.setCellStyle(columnStyle.getOrDefault(columnMeta.get(j).getClassField(), cellStyle));
            //    }
            //
            //}

            if (StringUtils.hasText(footer)) {
                //写入footer
                Row footerRow = sheet.createRow(rownum);
                Cell footerCell = footerRow.createCell(0);
                footerCell.setCellValue(footer);
                CellStyle footerStyle = workbook.createCellStyle();
                footerStyle.setAlignment(HorizontalAlignment.LEFT);
                footerStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
                Font footerFont = workbook.createFont();
                footerFont.setFontHeightInPoints((short) 16);
                footerStyle.setFont(footerFont);
                footerCell.setCellStyle(footerStyle);
                sheet.addMergedRegion(new CellRangeAddress(rownum, rownum + 3, 0, columnMeta.size() - 1));
            }

        }catch (Exception e){
            e.printStackTrace();
        }

    }


    //自定义合并单元格策略（适用于导出发货单）/纵向合并单元格
    private static void mergeCell(Sheet sheet, Integer index, List<Integer> list, List<Integer> cols) {
        CellRangeAddress cellRangeAddress = null;

        Integer lastRow = null;
        int temp = 0;
        for (int i = 0; i < list.size(); i++) {
            Integer group = list.get(i);

            lastRow = temp + group;

            if (group != 1) {
                for (Integer col : cols) {
                    if (index > lastRow) {
                        return;
                    }
                    cellRangeAddress = new CellRangeAddress(index, lastRow, col, col);
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }

            index = lastRow + 1;
            temp = lastRow;
        }

    }


    private static void writeData(HSSFWorkbook workbook, String sheetName, String[] columnName, String[][] dataList, boolean containsPic,String title, String footer){

        if (!StringUtils.hasText(sheetName) || columnName == null || columnName.length == 0 || dataList == null) {
            return;
        }

        try {
            if (workbook == null) {
                workbook = new HSSFWorkbook();
            }
            //创建sheet
            HSSFSheet sheet = workbook.createSheet(sheetName);
            sheet.setFitToPage(true);
            sheet.setHorizontallyCenter(true);
            PrintSetup printSetup = sheet.getPrintSetup();
            printSetup.setLandscape(true);
            printSetup.setPaperSize(PrintSetup.A4_PAPERSIZE);
            //设置列宽度
            for (int i = 0; i < columnName.length; i++) {
                sheet.setColumnWidth(i, 256 * 35);
            }

            //行号
            int rownum = 0;
            if (StringUtils.hasText(title)) {
                //标题行
                HSSFRow titleRow = sheet.createRow(rownum++);
                Cell titleCell = titleRow.createCell(0);
                titleCell.setCellValue(title);
                //标题行单元格样式
                Font titleFont = workbook.createFont();
                titleFont.setFontHeightInPoints((short) 12);
                titleFont.setBold(false);
                titleFont.setFontName("宋体");
                CellStyle titleStyle = workbook.createCellStyle();
                titleStyle.setAlignment(HorizontalAlignment.CENTER);
                titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
                titleStyle.setFont(titleFont);
                titleCell.setCellStyle(titleStyle);
                //合并单元格
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columnName.length - 1));
            }
            //设置列头
            HSSFRow columnHeaderRow = sheet.createRow(rownum++);
            HSSFCellStyle columnHeaderStyle = workbook.createCellStyle();
            // 设置单元格为水平对齐的类型
            columnHeaderStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            columnHeaderStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short) 12);
            font.setBold(false);
            font.setFontName("宋体");
            columnHeaderStyle.setFont(font);
            int cellLength = columnName.length;
            for (int i = 0; i < cellLength; i++) {
                HSSFCell cell = columnHeaderRow.createCell(i);
                cell.setCellValue(columnName[i]);
                cell.setCellStyle(columnHeaderStyle);
            }

            HSSFCellStyle cellStyle = workbook.createCellStyle();

            // 设置单元格为水平对齐的类型
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            //设置垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            HSSFFont font2 = workbook.createFont();
            font2.setFontHeightInPoints((short) 18);
            cellStyle.setFont(font2);
            cellStyle.setWrapText(true);

            // 添加数据
            HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
            for (int i = 0; i < dataList.length; i++) {
                HSSFRow dataRow = sheet.createRow(rownum++);
                short rowHeight = (short) (700);
                dataRow.setHeight(rowHeight);
                String[] columnData = dataList[i];
                for (int j = 0; j < cellLength; j++) {
                    HSSFCell cell2 = dataRow.createCell(j);
                    String columnsData = columnData[j];
                    if (containsPic) {
                        if (columnsData != null && columnsData.contains("http")) {
                            String resizeUrl = ImageSizeUtil.getResizeUrl(columnsData, 70);
                            URL url = new URL(resizeUrl);
                            //打开链接
                            HttpURLConnection conn = (HttpURLConnection)url.openConnection();
                            //设置请求方式为"GET"
                            conn.setRequestMethod("GET");
                            //超时响应时间为5秒
                            conn.setConnectTimeout(5 * 1000);
                            //通过输入流获取图片数据
                            InputStream inStream = conn.getInputStream();
                            //得到图片的二进制数据，以二进制封装得到数据，具有通用性
                            byte[] data = readInputStream(inStream);
                            //anchor主要用于设置图片的属性
                            // anchor主要用于设置图片的属性
                            // HSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2, int row2)
                            // dx1左上角所在cell的偏移x坐标，一般可设0，范围值为:0~1023,超过1023就到右侧相邻的单元格里
                            // dy1左上角所在cell的偏移y坐标，一般可设0，范围值为:0~256,超过256就到下方的单元格里
                            // dx2右下角所在cell的偏移x坐标，一般可设0，范围值为:0~1023,超过1023就到右侧相邻的单元格里
                            // dy2右下角所在cell的偏移y坐标，一般可设0，范围值为:0~256,超过256就到下方的单元格里
                            // col1左上角所在列，row1左上角所在行
                            // col2右下角所在列，row2右下角所在行
                            HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255,(short) j, i, (short) j, rownum);
                            //Sets the anchor type （图片在单元格的位置）
                            //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.
                            anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_DONT_RESIZE);
                            patriarch.createPicture(anchor, workbook.addPicture(data, HSSFWorkbook.PICTURE_TYPE_JPEG));
                            continue;
                        }
                    }
                    cell2.setCellValue(columnsData);
                    cell2.setCellStyle(cellStyle);
                }
            }

            if (StringUtils.hasText(footer)) {
                //写入footer
                HSSFRow footerRow = sheet.createRow(rownum);
                HSSFCell footerCell = footerRow.createCell(0);
                footerCell.setCellValue(footer);
                HSSFCellStyle footerStyle = workbook.createCellStyle();
                footerStyle.setAlignment(HorizontalAlignment.LEFT);
                footerStyle.setVerticalAlignment(VerticalAlignment.BOTTOM);
                HSSFFont footerFont = workbook.createFont();
                footerFont.setFontHeightInPoints((short) 16);
                footerStyle.setFont(footerFont);
                footerCell.setCellStyle(footerStyle);
                sheet.addMergedRegion(new CellRangeAddress(rownum, rownum + 3, 0, columnName.length - 1));
            }

        }catch (Exception e){
            e.printStackTrace();
        }

    }

    public static <T> List<T> readExcelToObject(InputStream inputStream, Class<T> clazz, boolean hasHeader){
        return readExcelToObject(inputStream, clazz, 0, hasHeader);
    }

    public static <T> List<T> readExcelToObject(InputStream inputStream, Class<T> clazz, Integer sheetNum, boolean hasHeader){

        List<T> list = Collections.emptyList();
        try {
            Workbook sheets = WorkbookFactory.create(inputStream);
            int numberOfSheets = sheetNum == null ? sheets.getNumberOfSheets() : sheetNum;
            List<ColumnMeta> columnMeta = getColumnMeta(clazz);
            Map<Integer, String> orderMapField = new HashMap<>(columnMeta.size());
            for (ColumnMeta meta : columnMeta){
                orderMapField.put(meta.getOrder(), meta.getClassField());
            }
            for (int i=0; i<=numberOfSheets; i++){
                Sheet sheet = sheets.getSheetAt(i);
                int lastRowNum = sheet.getLastRowNum();
                list = Lists.newArrayListWithCapacity(lastRowNum);
                int firstRow = 0;
                if (hasHeader){
                    //如果有标题行
                    firstRow = 1;
                }
                for (int rowNum=firstRow; rowNum<=lastRowNum; rowNum++){
                    T t = convertRowToObject(sheet.getRow(rowNum), orderMapField, clazz);
                    if (t != null){
                        list.add(t);
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }

    private static  <T> T convertRowToObject(Row row, Map<Integer, String> orderMapField, Class<T> clazz){

        T o;
        try {
            o = clazz.newInstance();
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        short lastCellNum = row.getLastCellNum();
        int blankCellCount = 0;
        for (int i=0; i<lastCellNum; i++){
            try {
                Cell cell  = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                CellType cellTypeEnum = cell.getCellTypeEnum();
                Object cellValue = null;
                switch (cellTypeEnum){
                    case STRING:
                        cellValue = cell.getStringCellValue();
                        break;
                    case NUMERIC:
                        cellValue = new BigDecimal(cell.getNumericCellValue());
                        break;
                    default:
                        blankCellCount ++;
                }
                if (cellValue != null){
                    String fieldName = orderMapField.get(i);
                    Field field = clazz.getDeclaredField(fieldName);
                    field.setAccessible(true);
                    Class<?> type = field.getType();
                    if (String.class.equals(type)){
                        field.set(o, cellValue.toString());
                    }else if (Integer.class.equals(type)){
                        field.set(o, ((BigDecimal) cellValue).intValue());
                    }else if (Long.class.equals(type)){
                        field.set(o, ((BigDecimal) cellValue).longValue());
                    }else if (Float.class.equals(type)){
                        field.set(o, ((BigDecimal) cellValue).floatValue());
                    }else if (Double.class.equals(type)){
                        field.set(o, ((BigDecimal) cellValue).doubleValue());
                    }else if (Short.class.equals(type)){
                        field.set(o, ((BigDecimal) cellValue).shortValue());
                    }
                }
            }catch (Exception e){
                e.printStackTrace();
                continue;
            }
        }
        if (blankCellCount == lastCellNum){
            return null;
        }
        return o;
    }

    /**
     * 获取不同的列样式
     * @param columnMeta
     * @param cellStyleTemplate 源样式
     * @param workbook
     * @return
     */
    private static Map<String, CellStyle> getColumnStyle(List<ColumnMeta> columnMeta, CellStyle cellStyleTemplate, Workbook workbook){
        Map<String, CellStyle> columnStyle = new HashMap<>(columnMeta.size());
        for (ColumnMeta meta : columnMeta) {
            if (meta.getCellStyles().length == 0) {
                continue;
            }
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.cloneStyleFrom(cellStyleTemplate);
            for (ExcelColumn.ExcelCellStyle style : meta.getCellStyles()) {
                style.setStyle(cellStyle);
            }
            columnStyle.put(meta.getClassField(), cellStyle);
        }
        return columnStyle;
    }

    private static List<Object> getChildrenRows(Object obj, String classField) {
        Class<?> clazz = obj.getClass();
        Object o = null;
        try {
            Field field = clazz.getDeclaredField(classField);
            field.setAccessible(true);
            o = field.get(obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
        if(!(o instanceof Collection)) {
            return Collections.emptyList();
        }
        return (List<Object>) o;
    }

    private static void setCellValue(Object obj, ColumnMeta columnMeta, Cell cell, Sheet sheet, Workbook workbook) {
        Class<?> clazz = obj.getClass();
        Object o = null;
        try {
            Field field = clazz.getDeclaredField(columnMeta.getClassField());
            field.setAccessible(true);
            o = field.get(obj);

        } catch (Exception e) {
            e.printStackTrace();
        }
        if(o == null){
            return;
        }
        switch (columnMeta.getCellValueType()) {
            case DOUBLE:
                if(o instanceof Double){
                    cell.setCellValue((Double) o);
                }
                cell.setCellValue(Double.parseDouble(o.toString()));
                break;
            case STRING:
                cell.setCellValue(o.toString());
                break;
            case PICTURE:
                String pictureUrl = o.toString();
                pictureUrl = pictureUrl.replaceAll(" ","");
                if ( !(pictureUrl.startsWith("http")) && !(pictureUrl.startsWith("https"))){
                    int indexOf = pictureUrl.indexOf("//");
                    String endStr = pictureUrl.substring(indexOf);
                    pictureUrl = "http:"+endStr;

                }
                if (pictureUrl.contains("bluetale")) {
                    pictureUrl = null;
                }
                if (StringUtils.isEmpty(pictureUrl)) {
                    cell.setCellValue(o.toString());
                } else {
                    FileOutputStream fileOut = null;
                    BufferedImage bufferImg = null;//logo
                    HttpURLConnection conn =null;
                    ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
                    URL url = null;
                    try {
                        XSSFDrawing patriarch = (XSSFDrawing) sheet.createDrawingPatriarch();
                        url = new URL(pictureUrl);
                        //打开链接
                        conn = (HttpURLConnection)url.openConnection();
                        //设置请求方式为"GET"
                        conn.setRequestMethod("GET");
                        //超时响应时间为50秒
                        conn.setConnectTimeout(50 * 1000);
                        //通过输入流获取图片数据
                        InputStream inStream = conn.getInputStream();
                        //得到图片的二进制数据，以二进制封装得到数据，具有通用性
                        byte[] byteData = readInputStream(inStream);

                        /**anchor主要用于设置图片的属性
                         * 该构造函数有8个参数
                         * 前四个参数是控制图片在单元格的位置，分别是图片距离单元格left，top，right，bottom的像素距离
                         * 后四个参数，前两个表示图片左上角所在的cellNum和 rowNum，后两个参数对应的表示图片右下角所在的cellNum和 rowNum，
                         * excel中的cellNum和rowNum的index都是从0开始的
                         *cell.getRowIndex();
                         *                     cell.getColumnIndex();
                         */
                        XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, (short)cell.getColumnIndex(), cell.getRowIndex(), (short) cell.getColumnIndex()+1,cell.getRowIndex()+1 );

                        //Sets the anchor type （图片在单元格的位置）
                        //0 = Move and size with Cells, 2 = Move but don't size with cells, 3 = Don't move or size with cells.

                        anchor.setAnchorType(ClientAnchor.AnchorType.MOVE_AND_RESIZE);//设置图片随单元移动调整大小

                        patriarch.createPicture(anchor, workbook.addPicture(byteData, XSSFWorkbook.PICTURE_TYPE_PNG));
                    } catch (Exception e) {
                        System.err.println(pictureUrl);
                        e.printStackTrace();
                    }
                }
                break;
            default:
                throw new RuntimeException("Unsupported field format");
        }


    }




    //获取标记了ExcelColumnGroup注解的集合中的元素类型
    private static Class getColGroupElement(Object obj) {
        Class<?> clazz = obj.getClass();
        Field[] fields = clazz.getDeclaredFields();
        Class<?> aClass = null;
        for (Field field : fields){
            ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
            if (annotation == null){
                ExcelColumnGroup excelColumnGroup = field.getAnnotation(ExcelColumnGroup.class);
                if(excelColumnGroup == null) {
                    continue;
                }
                field.setAccessible(true);
                ParameterizedType listGenericType = (ParameterizedType)field.getGenericType();
                Type[] actualTypeArguments = listGenericType.getActualTypeArguments();

                try {
                    aClass = Class.forName(actualTypeArguments[actualTypeArguments.length - 1].getTypeName());
                } catch (ClassNotFoundException e) {
                    e.printStackTrace();
                }
            }
        }

        return aClass;
    }


    private static List<ColumnMeta> getColumnMeta(Class clazz){
        return getColumnMeta(clazz, null);
    }

    private static List<ColumnMeta> getColumnMeta(Class clazz, List<String> specificFields){
        try {
            return getColumnMeta(clazz, specificFields, null);
        } catch (Exception e) {
            return null;
        }
    }

    private static List<ColumnMeta> getColumnMeta(Class clazz, List<String> specificFields, Field parentField) throws IllegalAccessException, ClassNotFoundException {
        Field[] fields = clazz.getDeclaredFields();
        List<ColumnMeta> columnsMetaList = new ArrayList<>();
        for (Field field : fields){
            if (CollectionUtils.isEmpty(specificFields) || specificFields.contains(field.getName())){
                ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
                if (annotation == null){
                    ExcelColumnGroup excelColumnGroup = field.getAnnotation(ExcelColumnGroup.class);
                    if(excelColumnGroup == null) {
                        continue;
                    }
                    field.setAccessible(true);
                    ParameterizedType listGenericType = (ParameterizedType)field.getGenericType();
                    Type[] actualTypeArguments = listGenericType.getActualTypeArguments();

                    Class<?> aClass1 = Class.forName(actualTypeArguments[actualTypeArguments.length - 1].getTypeName());
                    columnsMetaList.addAll(getColumnMeta(aClass1, null, field));
                }else {
                    if (!annotation.export()) {
                        continue;
                    }
                    ColumnMeta columnMeta = new ColumnMeta(annotation, field.getName(), parentField == null ? null : parentField.getName());
                    columnsMetaList.add(columnMeta);
                }
            }
        }
        columnsMetaList.sort(Comparator.comparingInt(ColumnMeta::getOrder));
        return columnsMetaList;
    }

    private static List<Integer> processMergeCellCols(List<ColumnMeta> columnMetas){
        List<Integer> list = Lists.newArrayListWithExpectedSize(columnMetas.size());
        for (int i = 0; i < columnMetas.size(); i++) {
            if(columnMetas.get(i).getParentClassField() != null) {
                continue;
            }
            list.add(i);
        }
        return list;
    }

    private static List<Integer> processMergeCell(List rows) throws IllegalAccessException {
        if(CollectionUtils.isEmpty(rows)) {
            return null;
        }
        Class<?> aClass = rows.get(0).getClass();
        Field[] fields = aClass.getDeclaredFields();
        Field groupField = null;
        for (Field field : fields){
            ExcelColumnGroup excelColumnGroup = field.getAnnotation(ExcelColumnGroup.class);
            if(excelColumnGroup != null) {
                groupField = field;
                break;
            }
        }

        if (groupField != null) {
            groupField.setAccessible(true);
            List<Integer> list = Lists.newArrayListWithExpectedSize(rows.size());
            for (Object row : rows) {
                Object o = groupField.get(row);
                if(!(o instanceof Collection)) {
                    return null;
                }
                Collection collection = (Collection) o;
                list.add(collection.size());
            }
            return list;
        }
        return Collections.emptyList();
    }

    private static byte[] readInputStream(InputStream inStream) throws IOException {
        ByteArrayOutputStream swapStream = new ByteArrayOutputStream();
        byte[] buff = new byte[1024];
        int rc;
        while ((rc = inStream.read(buff)) > 0) {
            swapStream.write(buff, 0, rc);
        }
        inStream.close();
        return swapStream.toByteArray();
    }


/** 读取产品给的运费列表用 */
//    public static void main(String[] args) {
//        try {
//            FileInputStream in = new FileInputStream("C:\\Users\\HASEE\\OneDrive\\桌面\\test.xlsx"); // 创建字节输入流
//            //1.读取上传的文件
//            XSSFWorkbook wb = new XSSFWorkbook(in);
//            //2.获取工作表对象
//            XSSFSheet sheet = wb.getSheetAt(0);
//            //3.得到行的迭代器
//            Iterator<Row> iterator = sheet.iterator();
//            int rowNum = 0;
//            while (iterator.hasNext()) {
//                Row row = iterator.next();
//                //跳过标题行
//                if (rowNum == 0) {
//                    rowNum++;
//                    continue;
//                }
//                //4.遍历,把每一行数据存到Object数组中
//                List<Long> shippingPriceList = Lists.newArrayList();
//                for (int i = 1; i < 41; i++) {
//                    Double origin = (double)getValue(row.getCell(i));
//                    String yuan = String.valueOf(roundUsingBigDecimal(origin));
//                    Long fen = MoneyFormatUtil.convertTOMoney(yuan);
//                    shippingPriceList.add(fen);
//                }
//                String join = Strings.join(shippingPriceList, ',');
//                System.err.println(join);
//            }
//
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//    }
//
//    public static double roundUsingBigDecimal(double digit) {
//        return BigDecimal.valueOf(digit)
//                .setScale(2, BigDecimal.ROUND_HALF_DOWN)
//                .floatValue();
//    }
//
//    private static Object getValue(Cell cell) {
//        switch (cell.getCellType()) {
//            case STRING:
//                return cell.getStringCellValue();
//            case BOOLEAN:
//                return cell.getBooleanCellValue();
//            case NUMERIC:// 数值和日期均是此类型,需进一步判断
//                if (DateUtil.isCellDateFormatted(cell)) {
//                    //是日期类型
//                    return cell.getDateCellValue();
//                } else {
//                    //是数值类型
//                    return cell.getNumericCellValue();
//                }
//            default:
//                return null;
//        }
//    }
}
